package persistence

import (
	"context"

	"Data-API-MCP/backend/internal/domain/entity"
	"database/sql"
	"errors"
	"fmt"
	"time"

	"github.com/google/uuid"
)

// ConnectionRepository 连接仓储实现
type ConnectionRepository struct {
	db *Database
}

// NewConnectionRepository 创建连接仓储
func NewConnectionRepository(db *Database) *ConnectionRepository {
	return &ConnectionRepository{
		db: db,
	}
}

// Create 创建连接
func (r *ConnectionRepository) Create(ctx context.Context, connection *entity.Connection) error {
	connection.ID = uuid.New().String() // 生成新的UUID
	connection.CreatedAt = time.Now()   // 设置创建时间
	connection.UpdatedAt = time.Now()   // 设置更新时间

	query := `
		INSERT INTO connections (
			id, name, type, host, port, username, password, database_name,
			description, max_idle_conns, max_open_conns, conn_max_lifetime,
			status, created_at, updated_at
		) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	`

	_, err := r.db.ExecContext(ctx, query,
		connection.ID, connection.Name, connection.Type, connection.Host,
		connection.Port, connection.Username, connection.Password,
		connection.DatabaseName, connection.Description, connection.MaxIdleConns,
		connection.MaxOpenConns, connection.ConnMaxLifetime, connection.Status,
		connection.CreatedAt, connection.UpdatedAt,
	)

	if err != nil {
		return fmt.Errorf("创建连接失败: %v", err)
	}

	return nil
}

// Update 更新连接
func (r *ConnectionRepository) Update(ctx context.Context, connection *entity.Connection) error {
	connection.UpdatedAt = time.Now()

	query := `
		UPDATE connections SET
			name = ?, type = ?, host = ?, port = ?, username = ?,
			password = ?, database_name = ?, description = ?,
			max_idle_conns = ?, max_open_conns = ?, conn_max_lifetime = ?,
			status = ?, updated_at = ?
		WHERE id = ?
	`

	result, err := r.db.ExecContext(ctx, query,
		connection.Name, connection.Type, connection.Host, connection.Port,
		connection.Username, connection.Password, connection.DatabaseName,
		connection.Description, connection.MaxIdleConns, connection.MaxOpenConns,
		connection.ConnMaxLifetime, connection.Status, connection.UpdatedAt,
		connection.ID,
	)

	if err != nil {
		return fmt.Errorf("更新连接失败: %v", err)
	}

	rows, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("获取影响行数失败: %v", err)
	}

	if rows == 0 {
		return errors.New("连接不存在")
	}

	return nil
}

// Delete 删除连接
func (r *ConnectionRepository) Delete(ctx context.Context, id string) error {
	query := "DELETE FROM connections WHERE id = ?"

	result, err := r.db.ExecContext(ctx, query, id)
	if err != nil {
		return fmt.Errorf("删除连接失败: %v", err)
	}

	rows, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("获取影响行数失败: %v", err)
	}

	if rows == 0 {
		return errors.New("连接不存在")
	}

	return nil
}

// FindByID 根据ID查找连接
func (r *ConnectionRepository) FindByID(ctx context.Context, id string) (*entity.Connection, error) {
	query := `
		SELECT id, name, type, host, port, username, password,
			database_name, description, max_idle_conns, max_open_conns,
			conn_max_lifetime, status, created_at, updated_at
		FROM connections
		WHERE id = ?
	`

	connection := &entity.Connection{}
	err := r.db.QueryRowContext(ctx, query, id).Scan(
		&connection.ID, &connection.Name, &connection.Type, &connection.Host,
		&connection.Port, &connection.Username, &connection.Password,
		&connection.DatabaseName, &connection.Description, &connection.MaxIdleConns,
		&connection.MaxOpenConns, &connection.ConnMaxLifetime, &connection.Status,
		&connection.CreatedAt, &connection.UpdatedAt,
	)

	if err == sql.ErrNoRows {
		return nil, errors.New("连接不存在")
	}

	if err != nil {
		return nil, fmt.Errorf("查询连接失败: %v", err)
	}

	return connection, nil
}

// FindByName 根据名称查找连接
func (r *ConnectionRepository) FindByName(ctx context.Context, name string) (*entity.Connection, error) {
	query := `
		SELECT id, name, type, host, port, username, password,
			database_name, description, max_idle_conns, max_open_conns,
			conn_max_lifetime, status, created_at, updated_at
		FROM connections
		WHERE name = ?
	`

	connection := &entity.Connection{}
	err := r.db.QueryRowContext(ctx, query, name).Scan(
		&connection.ID, &connection.Name, &connection.Type, &connection.Host,
		&connection.Port, &connection.Username, &connection.Password,
		&connection.DatabaseName, &connection.Description, &connection.MaxIdleConns,
		&connection.MaxOpenConns, &connection.ConnMaxLifetime, &connection.Status,
		&connection.CreatedAt, &connection.UpdatedAt,
	)

	if err == sql.ErrNoRows {
		return nil, errors.New("连接不存在")
	}

	if err != nil {
		return nil, fmt.Errorf("查询连接失败: %v", err)
	}

	return connection, nil
}

// List 获取连接列表
func (r *ConnectionRepository) List(ctx context.Context, offset, limit int) ([]*entity.Connection, error) {
	query := `
		SELECT id, name, type, host, port, username, password,
			database_name, description, max_idle_conns, max_open_conns,
			conn_max_lifetime, status, created_at, updated_at
		FROM connections
		LIMIT ? OFFSET ?
	`

	rows, err := r.db.QueryContext(ctx, query, limit, offset)
	if err != nil {
		return nil, fmt.Errorf("查询连接列表失败: %v", err)
	}
	defer rows.Close()

	var connections []*entity.Connection
	for rows.Next() {
		connection := &entity.Connection{}
		err := rows.Scan(
			&connection.ID, &connection.Name, &connection.Type, &connection.Host,
			&connection.Port, &connection.Username, &connection.Password,
			&connection.DatabaseName, &connection.Description, &connection.MaxIdleConns,
			&connection.MaxOpenConns, &connection.ConnMaxLifetime, &connection.Status,
			&connection.CreatedAt, &connection.UpdatedAt,
		)
		if err != nil {
			return nil, fmt.Errorf("扫描连接数据失败: %v", err)
		}
		connections = append(connections, connection)
	}

	if err = rows.Err(); err != nil {
		return nil, fmt.Errorf("遍历连接数据失败: %v", err)
	}

	return connections, nil
}

// Count 获取连接总数
func (r *ConnectionRepository) Count(ctx context.Context) (int64, error) {
	var count int64
	err := r.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM connections").Scan(&count)
	if err != nil {
		return 0, fmt.Errorf("获取连接总数失败: %v", err)
	}
	return count, nil
}

// ListByType 获取指定类型的连接列表
func (r *ConnectionRepository) ListByType(ctx context.Context, dbType string, offset, limit int) ([]*entity.Connection, error) {
	query := `
		SELECT id, name, type, host, port, username, password,
			database_name, description, max_idle_conns, max_open_conns,
			conn_max_lifetime, status, created_at, updated_at
		FROM connections
		WHERE type = ?
		LIMIT ? OFFSET ?
	`

	rows, err := r.db.QueryContext(ctx, query, dbType, limit, offset)
	if err != nil {
		return nil, fmt.Errorf("查询连接列表失败: %v", err)
	}
	defer rows.Close()

	var connections []*entity.Connection
	for rows.Next() {
		connection := &entity.Connection{}
		err := rows.Scan(
			&connection.ID, &connection.Name, &connection.Type, &connection.Host,
			&connection.Port, &connection.Username, &connection.Password,
			&connection.DatabaseName, &connection.Description, &connection.MaxIdleConns,
			&connection.MaxOpenConns, &connection.ConnMaxLifetime, &connection.Status,
			&connection.CreatedAt, &connection.UpdatedAt,
		)
		if err != nil {
			return nil, fmt.Errorf("扫描连接数据失败: %v", err)
		}
		connections = append(connections, connection)
	}

	if err = rows.Err(); err != nil {
		return nil, fmt.Errorf("遍历连接数据失败: %v", err)
	}

	return connections, nil
}

// CountByType 获取指定类型的连接总数
func (r *ConnectionRepository) CountByType(ctx context.Context, dbType string) (int64, error) {
	var count int64
	err := r.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM connections WHERE type = ?", dbType).Scan(&count)
	if err != nil {
		return 0, fmt.Errorf("获取连接总数失败: %v", err)
	}
	return count, nil
}

// TestConnection 测试连接
func (r *ConnectionRepository) TestConnection(ctx context.Context, connection *entity.Connection) error {
	// 根据连接类型创建测试连接
	testDB, err := NewDatabase(DatabaseConfig{
		Type: connection.Type,
		MySQL: MySQLConfig{
			Host:            connection.Host,
			Port:            connection.Port,
			Username:        connection.Username,
			Password:        connection.Password,
			Database:        connection.DatabaseName,
			MaxIdleConns:    connection.MaxIdleConns,
			MaxOpenConns:    connection.MaxOpenConns,
			ConnMaxLifetime: time.Duration(connection.ConnMaxLifetime) * time.Second,
		},
		PostgreSQL: PostgreSQLConfig{
			Host:            connection.Host,
			Port:            connection.Port,
			Username:        connection.Username,
			Password:        connection.Password,
			DBName:          connection.DatabaseName,
			SSLMode:         "disable",
			MaxIdleConns:    connection.MaxIdleConns,
			MaxOpenConns:    connection.MaxOpenConns,
			ConnMaxLifetime: time.Duration(connection.ConnMaxLifetime) * time.Second,
		},
	})

	if err != nil {
		return fmt.Errorf("创建测试连接失败: %v", err)
	}
	defer testDB.Close()

	return nil
}
